USE [DSRSystem]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetNonControversySources]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetNonControversySources]
GO

CREATE PROCEDURE GetNonControversySources
	@companiesIds varchar(max) = null
AS
BEGIN
	declare @selectedCompanies table (Id uniqueidentifier, FinalVersion int)
	insert @selectedCompanies select c.CompanyId, c.WorkingVersion - 1 from DSRSystem.dbo.fSplitIDs(@companiesIds, ',') sc 
										join Companies c on c.CompanyId = sc.ID and c.WorkingVersion > 1002 and c.IsParked = 0 and c.IsControversyResearchOnly = 0

	if not exists (select * from @selectedCompanies)
		insert @selectedCompanies select CompanyId, WorkingVersion - 1 from Companies where WorkingVersion > 1002 and IsParked = 0 and IsControversyResearchOnly = 0 

	select s.CompanyId, s.SourceId, CONVERT(nvarchar(max), s.Text) SourceText, i.Number IndicatorNumber from Sources s
	join @selectedCompanies sc on sc.Id = s.CompanyId
	join IndicatorSources indS on s.SourceId = inds.SourceId
	join Indicators i on inds.IndicatorId = i.IndicatorId 
							and i.IndicatorTypeId = 1 
							and i.Version = sc.FinalVersion 
							and i.Number not in ('G.1.5','G.2.13','G.3.4','S.1.7','S.2.3','S.3.3','S.4.3','E.1.12','E.2.2','E.3.2')
	union
	select s.CompanyId, s.SourceId, CONVERT(nvarchar(max), s.Text) SourceText, i.Number IndicatorNumber from Sources s
	join @selectedCompanies sc on sc.Id = s.CompanyId
	join CommentsAlphanumericSources canS on s.SourceId = cans.SourceId
	join CommentsAlphanumeric can on cans.CommentsAlphanumericId = can.CommentsAlphanumericId
	join Indicators i on can.IndicatorId = i.IndicatorId 
							and i.IndicatorTypeId = 1 
							and i.Version = sc.FinalVersion 
							and i.Number not in ('G.1.5','G.2.13','G.3.4','S.1.7','S.2.3','S.3.3','S.4.3','E.1.12','E.2.2','E.3.2')
	order by CompanyId, SourceId
END	
GO